using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAOLayer
{
    public class LoaiKhachDao
    {
        public static DataTable LayDanhSachLoaiKhach()
        {
            DataTable dt = new DataTable();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select * From LOAIKHACH";
            //'B4: Thuc thi chuoi strSQL
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            // 'B5: Dong ket noi CSDL
            cn.Close();
            return dt;

        }
        public static LoaiKhachDto LayMotLoaiKhach(int ma)
        {
            LoaiKhachDto lkhach = new LoaiKhachDto();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select * From LOAIKHACH Where MaLoaiKhach = ? ";
            //'B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaLoaiKhach", OleDbType.Integer);
            cmd.Parameters[("@MaLoaiKhach")].Value = ma;
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                lkhach.MaLoaiKhach = (int)dr["MaLoaiKhach"];
                lkhach.TenLoaiKhach= (string)dr["TenLoaiKhach"];
                lkhach.HeSoKhach = (double)dr["HeSoKhach"];

            }
            cn.Close();
            return lkhach;

        }
        public static void Them(LoaiKhachDto lp)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into LOAIKHACH ( TenLoaiKhach , HeSoKhach ) values(?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenLoaiKhach", OleDbType.WChar);
            cmd.Parameters["@TenLoaiKhach"].Value = lp.TenLoaiKhach;
            cmd.Parameters.Add("@HeSoKhach", OleDbType.Double);
            cmd.Parameters["@HeSoKhach"].Value = lp.HeSoKhach;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            lp.MaLoaiKhach = Convert.ToInt32(cmd.ExecuteScalar());
            // B5: Dong ket noi CSDL
            cn.Close();
        }
        public static void CapNhatXuongCSDL(LoaiKhachDto lk)
        {
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Update LOAIKHACH Set TenLoaiKhach = ? ,HeSoKhach = ?  Where MaLoaiKhach = ?  ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenLoaiKhach", OleDbType.WChar);
            cmd.Parameters["@TenLoaiKhach"].Value = lk.TenLoaiKhach;
            cmd.Parameters.Add("@HeSoKhach", OleDbType.Double);
            cmd.Parameters["@HeSoKhach"].Value = lk.HeSoKhach;
            cmd.Parameters.Add("@MaLoaiKhach", OleDbType.Integer);
            cmd.Parameters[("@MaLoaiKhach")].Value = lk.MaLoaiKhach;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public static void Xoa(int MaLKhach)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Delete From LOAIKHACH Where MaLoaiKhach = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaLoaiKhach", OleDbType.Integer);
            cmd.Parameters["@MaLoaiKhach"].Value = MaLKhach;

            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }
        public static LoaiKhachDto LayTenMotLoaiKhach(string ten)
        {
            LoaiKhachDto lkhach = new LoaiKhachDto();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = "Select * From LOAIKHACH Where TenLoaiKhach = ? ";
            //'B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenLoaiKhach", OleDbType.WChar);
            cmd.Parameters[("@TenLoaiKhach")].Value = ten;
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                lkhach.MaLoaiKhach = (int)dr["MaLoaiKhach"];
                lkhach.TenLoaiKhach = (string)dr["TenLoaiKhach"];
                lkhach.HeSoKhach = (double)dr["HeSoKhach"];

            }
            cn.Close();
            return lkhach;

        }
      
    }
}
